package com.jp.fm.importer;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.FileUtils;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.dao.CategoryDao;

public final class USBankHomeMortgageImporter {

	private static final SimpleDateFormat SDF = new SimpleDateFormat(
			"MM/dd/yyyy");

	private USBankHomeMortgageImporter() {
	}

	/**
	 * @param args
	 */
	public static void main(final String[] args) {

		String wholeFile = null;
		Integer insertCount = 0;

		try {
			wholeFile = FileUtils.readFileToString(new File(args[0]));
		} catch (IOException e) {
			e.printStackTrace();
		}

		if (null != wholeFile) {

			CSVParser parser = null;
			try {
				parser = new CSVParser(wholeFile, CSVFormat.DEFAULT);
			} catch (IOException e1) {
				e1.printStackTrace();
			}

			if (null != parser) {

				for (Iterator<CSVRecord> iterator = parser.iterator(); iterator
						.hasNext();) {
					CSVRecord record = iterator.next();

					Connection c = null;
					PreparedStatement p = null;

					if ("Payment Applied".equals(record.get(1))) {

						try {

							Date date = SDF.parse(record.get(0));
							String acct = args[1];
							String desc = record.get(1);

							Number number = NumberFormat.getInstance().parse(
									record.get(5).substring(1));
							Float amount = number.floatValue();

							c = HomeFinanceRunner.getConnection();

							p = c.prepareStatement("INSERT INTO PUBLIC.TRANSACTIONS "
									+ "( DATE, ACCOUNT, AMOUNT, CATEGORY_ID, DESCRIPTION) "
									+ "VALUES (?,?,?,?,?)");

							p.setDate(1, new java.sql.Date(date.getTime()));
							p.setString(2, acct);
							p.setFloat(3, amount);
							p.setInt(4, new CategoryDao()
									.findByName("Mortgage").getId());
							p.setString(5, desc);

							p.executeUpdate();

							c.commit();
							insertCount++;

						} catch (SQLException e) {
							e.printStackTrace();
						} catch (ParseException pe) {
							pe.printStackTrace();
						} catch (IOException e) {
							e.printStackTrace();
						} finally {

							HomeFinanceRunner.closeConnection(c, p, null);
						}
					}
				}
			}
		}

		System.out.println(insertCount + " records inserted");

	}

}
